package edu.uwm.universitydb.tests;

public class Query2 extends PerformanceTest {


	@Override
	public String getName() {
		return "Project with the highest budget/student ratio";
	}

	@Override
	public String getQuery() {
		return "SELECT pno, MAX(budget/ppl) " +
				"FROM ( " +
				"SELECT proj.pno as pno, proj.budget as budget, " +
				"COUNT(*) as ppl " +
				"FROM project proj, supervises sup, grad_student stu " +
				"WHERE proj.pno = sup.pno AND stu.grad_ssn = sup.grad_ssn " +
				"GROUP BY sup.pno) as studnts; ";

	}

	@Override
	public String[] getAddIndex() {
		return new String [] {
				"create index budget_index on project (budget)  using btree;",
				"create index student_index on grad_student (grad_ssn) using hash;",
				"create index proj_index on project (pno) using hash;"
		};
	}

	@Override
	String[] getDropIndex() {
		return new String [] {
				"drop index budget_index on project",
				"drop index student_index on grad_student",
				"drop index proj_index on project"
		};
	}
}
